import pymysql
from dbutils.pooled_db import PooledDB


class My_Pool(object):
    def __init__(self):
        self.pool = PooledDB(
            creator=pymysql,  # 使用连接数据库的模块
            maxconnections=6,  # 连接池允许的最大连接数，0和None表示不限制连接数
            mincached=1,   # 初始化时，连接池中至少创建的连接，0表示不创建
            blocking=True,  # 连接池中如果没有可用连接后，是否阻塞等待。True：等待，False：不等待然后报错
            ping=0,  # ping MySQL服务端，检查是否服务可用。# 如：0 = None = never，1=default

            host='127.0.0.1',
            port=3306,
            user='root',
            password='123456',
            database='my_batis',
            charset='utf8'
        )

    def open(self):
        conn = self.pool.connection()
        cursor = conn.cursor()
        # cursor = conn.cursor(cursor=cursor.DictCursor)
        return conn, cursor

    def close(self, cursor, conn):
        cursor.close()
        # 将连接放回到连接池
        conn.close()

    def fetchall(self, sql, *args):
        """ 获取所有数据 """
        conn, cursor = self.open()
        cursor.execute(sql, *args)
        result = cursor.fetchall()
        self.close(cursor, conn)
        return result

    def fetchone(self, sql, *args):
        """ 获取单条数据 """
        conn, cursor = self.open()
        cursor.execute(sql, *args)
        result = cursor.fetchone()
        self.close(cursor, conn)
        return result

    def fetchmany(self, sql, *args, n):
        """ 获取前n行数据 """
        conn, cursor = self.open()
        cursor.execute(sql, *args)
        result = cursor.fetchmany(n)
        self.close(cursor, conn)
        return result


if __name__ == '__main__':
    db = My_Pool()
    data = db.fetchone('select * from user where username = %s and id = %s', ('wang1', '8'))
    print(data)
